﻿using Ace.Application;
using Ace.Data;
using Ace.Entity.Wiki;
using Ace.Exceptions;
using Ace.IdStrategy;
using Chloe;
using Chloe.MySql;
using Chloe.SqlServer;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ace.Application.Wiki
{
    public interface IUsersPayService : IAppService
    { 
        void Add(AddUsersPayInput input);
        void Update(UpdateUsersPayInput input);
        void Delete(string id );

        UsersPay GetModel(string Id);

        UsersPay GetModle2(string UserID);

        UsersPay GetModelByTransaction_id(string transaction_id);

        List<UsersPayInfo> GetList(string UserID);

        PagedData<UsersPay> GetPageData(Pagination page,   string keyword);

        PagedData<UsersPayInfo> GetUsersPayInfoList(Pagination page,string UserID="", string time1 = "", string time2 = "", string keywords = "");
    }

    public class UsersPayService : AppServiceBase<UsersPay>, IUsersPayService
    {
        public UsersPayService(IDbContext dbContext, IServiceProvider services) : base(dbContext, services)
        {
        }


        public List<UsersPayInfo> GetList(string UserID)
        {
            string sql = "select a.*,b.TrueName,b.Mobile from users_pay a left join users b on a.UserID=b.Id where 1=1  ";

            if(!string.IsNullOrEmpty(UserID))
            {
                sql += " and a.UserID='" + UserID + "' ";
            }

            sql += " order by transaction_date desc ";

            List<UsersPayInfo> list = this.DbContext.SqlQuery<UsersPayInfo>(sql).ToList();
            return list;
        }


        public PagedData<UsersPayInfo> GetUsersPayInfoList(Pagination page,string UserID="", string time1 = "", string time2 = "", string keywords="")
        {
            string strFileds = " a.*,b.TrueName,b.Mobile ";

            string strWhere = " 1=1 ";

            if(!string.IsNullOrEmpty(UserID))
            {
                strWhere += " and a.UserID='"+ UserID + "' ";
            }
            if (!string.IsNullOrEmpty(time1))
            {
                strWhere += " and a.transaction_date>='" + time1 + " 00:00:00'";
            }
            if (!string.IsNullOrEmpty(time2))
            {
                strWhere += " and a.transaction_date<='" + time2 + " 23:59:59'";
            }
            if (!string.IsNullOrEmpty(keywords))
            {
                strWhere += " and ( a.transaction_id like '%"+ keywords + "%' or b.TrueName like '%" + keywords + "%' or b.Mobile like '%" + keywords + "%' ) ";
            }
            
            DbParam _totalcount = new DbParam("?_totalcount", null, typeof(int)) { Direction = ParamDirection.Output };
            DbParam _pagecount = new DbParam("?_pagecount", null, typeof(int)) { Direction = ParamDirection.Output };

            DbParam[] dbs = new DbParam[] {
                new DbParam("?_fields", strFileds),
                new DbParam("?_tables", "  users_pay a left join users b on a.UserID=b.Id "),
                new DbParam("?_where",strWhere),
                new DbParam("?_orderby", "a.transaction_date desc"),
                new DbParam("?_pageindex", page.Page),
                new DbParam("?_pageSize", page.PageSize),
                _totalcount,
                _pagecount
        };

            List<UsersPayInfo> list = this.DbContext.SqlQuery<UsersPayInfo>("sp_viewPage", System.Data.CommandType.StoredProcedure, dbs).ToList();

            PagedData<UsersPayInfo> pagedData = new PagedData<UsersPayInfo>();
            pagedData.CurrentPage = page.Page;
            pagedData.Models = list;
            pagedData.PageSize = page.PageSize;
            pagedData.TotalCount = Convert.ToInt32(_totalcount.Value);
            pagedData.TotalPage = Convert.ToInt32(_pagecount.Value);

            return pagedData;
        }



        public void Add(AddUsersPayInput input)
        {
            this.InsertFromDto(input);
        }
        public void Update(UpdateUsersPayInput input)
        {
            this.UpdateFromDto(input);
        }

        public UsersPay GetModel(string Id)
        {
            return  this.Query.Where(a => a.Id == Id).FirstOrDefault();
        }

        public UsersPay GetModelByTransaction_id(string transaction_id)
        {
            return this.Query.Where(a => a.transaction_id == transaction_id).FirstOrDefault();
        }

        public void Delete(string id)
        {
            this.DbContext.Delete<UsersPay>(a => a.Id == id);
        }


        public PagedData<UsersPay> GetPageData(Pagination page, string keyword="")
        {
            var q = this.DbContext.Query<UsersPay>();

            q = q.OrderByDesc(a => a.expire_date);


            PagedData<UsersPay> pagedData = q.TakePageData(page);
            
            return pagedData;
        }

         
        public UsersPay GetModle2(string UserID)
        {
            string sql = "SELECT * FROM users_pay WHERE UserID='"+ UserID + "' AND expire_date>NOW() AND Title LIKE '%年费%' limit 1";
            UsersPay pay = this.DbContext.SqlQuery<UsersPay>(sql).FirstOrDefault();
            return pay;
        }



    }

}
